﻿CREATE PROCEDURE [dbo].[utl_AssignExecPermissions]

AS
BEGIN
	DECLARE @AllProcs         TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ProcedureName sysname)
	DECLARE @ID               INT
	DECLARE @ID2              INT
	DECLARE @SQL              NVARCHAR(MAX)
	DECLARE @ProcedureName    sysname
	DECLARE @DeveloperAccount sysname

	EXECUTE utl_AssignCRUDExecPermissions

	INSERT INTO @AllProcs (ProcedureName)
		SELECT ProcedureName FROM dbo.fn_CustomProcs()
	
	SELECT @ID2 = MIN(d.ID)
	FROM   #Developer d
	
	WHILE @ID2 IS NOT NULL
	BEGIN
	
		SELECT @DeveloperAccount = d.DeveloperAccount
		FROM   #Developer d
		WHERE  d.ID = @ID2
		
		SELECT @ID = MIN(ID)
		FROM   @AllProcs
		
		WHILE @ID IS NOT NULL
		BEGIN
			SELECT @ProcedureName = ProcedureName
			FROM   @AllProcs
			WHERE  ID = @ID
		    
			SET @SQL = 'GRANT EXECUTE ON ' + dbo.fn_QuoteName(@ProcedureName) + ' TO [' + @DeveloperAccount + ']'
			EXECUTE sp_executesql @SQL

			SELECT @ID = MIN(ID)
			FROM   @AllProcs
			WHERE  ID > @ID
		END
		
		SELECT @ID2 = MIN(d.ID)
		FROM   #Developer d
		WHERE  d.ID > @ID2
	END
END

